/**
 * Book数据模型模块
 * model\sqlite\book.js
 */

//导入SQLite3模块
const sqlite3 = require("sqlite3").verbose();
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");

/**
 * BookDB构造函数
 * @constructor
 */
function BookDB() {
  this.dbFile = config.dbFile;
  this.instance = null; //BookDB数据库实例
  this.db = null; //SQLite3实例
}

/**
 * 创建Book对象
 */
BookDB.getInstance = function () {
  if (!this.instance) {
    this.instance = new BookDB();
  }
  return this.instance;
};

/**
 * 连接数据库
 * @returns {Promise}
 */
BookDB.prototype.connect = function () {
  return new Promise((resolve, reject) => {
    this.db = new sqlite3.Database(this.dbFile, (err) => {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve("connect ok.");
      }
    });
  });
};

/**
 * 关闭数据库
 * @returns  {Promise}
 */
BookDB.prototype.close = function () {
  return new Promise((resolve, reject) => {
    this.db.close((err) => {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve("close ok.");
      }
    });
  });
};

/**
 * 获取指定ID的书籍信息
 * @param {Number} bookId 书籍ID
 * @returns  {Promise}
 */
BookDB.prototype.find = function (bookId) {
  return new Promise((resolve, reject) => {
    let sql = "SELECT * FROM books WHERE id = ?";
    let params = [bookId];
    this.db.get(sql, params, (err, result) => {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

/**
 * 获取书籍信息列表
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns {Promise}
 */
BookDB.prototype.findAll = function (
  limit = -1,
  offset = -1,
  orderBy = "id",
  sort = "DESC"
) {
  return new Promise((resolve, reject) => {
    let sql = "";
    let params = [];
    if (limit === -1) {
      sql = `SELECT * FROM books ORDER BY ${orderBy} ${sort}`;
    } else if (offset === -1) {
      sql = `SELECT * FROM books ORDER BY ${orderBy} ${sort} LIMIT ?`;
      params[0] = limit;
    } else {
      sql = `SELECT * FROM books ORDER BY ${orderBy} ${sort} LIMIT ? OFFSET ?`;
      params[0] = limit;
      params[1] = offset;
    }
    this.db.all(sql, params, (err, result) => {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

/**
 * 新增书籍
 * @param {Object} book 书籍数据
 * @returns {Promise}
 */
BookDB.prototype.add = function (book) {
  return new Promise((resolve, reject) => {
    let sql = `INSERT INTO books (
        title,pic,local_pic,author,publisher,producer,
        subtitle,originalTitle,translator,pubdate,pages,price, 
        binding,series,isbn,intro,doubanId,created_time, 
        updated_time
        ) VALUES (
            ?,?,?,?,?,?,
            ?,?,?,?,?,?,
            ?,?,?,?,?,?,
            ?
        );`;
    let params = [
      book.title,
      book.pic,
      book.localPic,
      book.author,
      book.publisher,
      book.producer,
      book.subtitle,
      book.originalTitle,
      book.translator,
      book.pubdate,
      book.pages,
      book.price,
      book.binding,
      book.series,
      book.isbn,
      book.intro,
      book.doubanId,
      book.createdTime,
      book.updatedTime,
    ];

    this.db.run(sql, params, function (err, result) {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(this.lastID); //插入的数据的自增ID
      }
    });
  });
};

/**
 * 修改书籍
 * @param {Object} book 书籍数据
 * @returns {Promise}
 */
BookDB.prototype.update = function (book) {
  return new Promise((resolve, reject) => {
    let sql = `UPDATE books SET
 title=?,pic=?,local_pic=?,author=?, 
 publisher=?, producer=?,subtitle=?,originalTitle=?, 
 translator=?, pubdate=?, pages=?, price=?, 
 binding=?, series=?, isbn=?, intro=?, 
 doubanId=?, updated_time=?
 WHERE id=?
 ;`;
    let params = [
      book.title,
      book.pic,
      book.localPic,
      book.author,
      book.publisher,
      book.producer,
      book.subtitle,
      book.originalTitle,
      book.translator,
      book.pubdate,
      book.pages,
      book.price,
      book.binding,
      book.series,
      book.isbn,
      book.intro,
      book.doubanId,
      book.updatedTime,
      book.id,
    ];

    this.db.run(sql, params, function (err, result) {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(this.changes); //影响的记录数
      }
    });
  });
};

/**
 * 删除书籍
 * @param {Number} bookId 书籍ID
 * @returns {Promise}
 */
BookDB.prototype.remove = function (bookId) {
  return new Promise((resolve, reject) => {
    let sql = "DELETE FROM books WHERE id = ?";
    let params = [bookId];
    this.db.run(sql, params, function (err, result) {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(this.changes); //影响的记录数
      }
    });
  });
};

/**
 * 获取书籍总数
 * @returns {Promise}
 */
BookDB.prototype.getCount = function () {
  return new Promise((resolve, reject) => {
    let sql = "SELECT count(1) AS total FROM books";
    let params = [];
    this.db.get(sql, params, function (err, result) {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(result); //影响的记录数
      }
    });
  });
};

/**
 * 获取书籍信息列表
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns {Promise}
 */
BookDB.prototype.search = function (q, limit = -1, offset = -1) {
  return new Promise((resolve, reject) => {
    let sql = "";
    q = `%${q}%`;
    let params = [q, q];
    if (limit === -1) {
      sql = "SELECT * FROM books WHERE title LIKE ? OR author LIKE ?";
    } else if (offset === -1) {
      sql = "SELECT * FROM books WHERE title LIKE ? OR author LIKE ? LIMIT ?";
      params.push(limit);
    } else {
      sql =
        "SELECT * FROM books WHERE title LIKE ? OR author LIKE ? LIMIT ? OFFSET ?";
      params.push(limit);
      params.push(offset);
    }
    this.db.all(sql, params, (err, result) => {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

/**
 * 获取指定ISBN的书籍信息
 * @param {String} bookIsbn 书籍ISBN
 * @returns {Promise}
 */
BookDB.prototype.findByIsbn = function (bookIsbn) {
  return new Promise((resolve, reject) => {
    let sql = "SELECT * FROM books WHERE isbn = ?";
    let params = [bookIsbn];
    this.db.get(sql, params, (err, result) => {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

module.exports = BookDB;
